package com.example.studyplanning.mapper;

import com.example.studyplanning.entity.Task;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface TaskMapper {
    //3.2 显示计时时间（从所有信息中选中字段展示）通过用户ID任务表和用户表连接查询所有任务信息
    @Select("select task.*,DATE_FORMAT(taskDate,'%Y-%m-%d') as date_time  from task")
    List<Task> findAllTask();

    //2.3添加列表任务
    @Insert("insert into task(userID,taskID,taskName,taskClass,taskType,taskTime,taskLive) values (#{userID},#{taskID},#{taskName},#{taskClass},#{taskType},#{taskTime},#{taskLive})")
    Integer insertTask(Task task);

    //2.5选中任务进行删除（根据任务ID）（3.5 删除任务）
    @Delete("delete from task where taskID=#{taskID}")
    Integer DelTaskByID(Integer taskID);

    //2.4修改任务 更新任务信息（3.4修改任务）
    @Update("update task set userID=#{userID},taskName=#{taskName},taskClass=#{taskClass},taskTime=#{taskTime} where taskID=#{taskID}")
    Integer UpdateTask(Task task);

    //3.3任务提前完成,输入计时任务ID，将任务状态更新为完成状态
    @Update("update task set taskLive=true where taskID=#{taskID}")
    Integer UpdateTaskLive(Integer taskID);

    //任务误操作，将任务已完成改为任务未完成
    @Update("update task set taskLive=false where taskID=#{taskID}")
    Integer UpdateLive(Integer taskID);

    //根据任务名进行模糊查询
    @Select("select task.*,userName from task join user on task.userID=user.userID where task.taskName like '%${taskName}%'")
    List<Task> findTaskByName(@Param("taskName") String taskName);

    //4.3任务显示页面：根据所选日期显示任务名称，任务标签，任务类型
    @Select("select * from\n" +
            "(select task.*, date(taskDate) as date_time \n" +
            "from task) t1\n" +                        //单引号里面用美元符号$
            "where date_format(date_time,'%Y-%m-%d') = '${date_time}' and userID=#{userID} order by taskLive")
    List<Task> SelectByDate(@Param("date_time") String date_time,@Param("userID") Integer userID);

    //2.1展示今日列表任务
    @Select("select * from\n" +
            "(select task.*, date(taskDate) as date_time \n" +
            "from task) t1\n" +                        //单引号里面用美元符号$
            "where date_format(date_time,'%Y-%m-%d') = '${date_time}' and userID=#{userID} and taskType=false order by taskLive")
    List<Task> findByDate(@Param("date_time") String date_time,@Param("userID") Integer userID);

    //2.1展示今日专注任务
    @Select("select * from (select task.*, date(taskDate) as date_time from task) t1 where date_format(date_time,'%Y-%m-%d') = '${date_time}' and userID=#{userID} and taskType=true order by taskLive")
    List<Task> findListByDate(@Param("date_time") String date_time ,@Param("userID") Integer userID);

    //（后台）2.1任务查询 根据输入内容得到相关任务信息(taskType和taskLive都设置为int类型，在数据库中的值为0或1）
    @Select("select t1.* from (select task.*, date(taskDate) as date_time from task) t1 join user on t1.userID=user.userID where date_time = '${date_time}' and taskID like '%${taskID}' and taskName like '%${taskName}%' and taskClass like '%${taskClass}%' and taskType like '%${taskType}' and taskTime like '%${taskTime}' and userID like '%${userID}' and taskLive like '%${taskLive}'")
    List<Task> SelectTask(@Param("date_time") String date_time,@Param("taskID") Integer taskID,@Param("taskName") String taskName,@Param("taskClass") String taskClass,@Param("taskType") Integer taskType,@Param("taskTime") Integer taskTime,@Param("userID") Integer userID,@Param("taskLive") Integer taskLive);

    //(辅助2.1任务查询)
    @Select("select *,date_format(taskDate,'%Y-%m-%d') as date_time from task join user on task.userID=user.userID where taskID like '%${taskID}%' and taskName like '%${taskName}%' and taskClass like '%${taskClass}%' and taskType like '%${taskType}%' and taskTime like '%${taskTime}%' and task.userID like '%${userID}%' and taskLive like '%${taskLive}%'")
    List<Task> SelectTheTask(@Param("taskID") Integer taskID,@Param("taskName") String taskName,@Param("taskClass") String taskClass,@Param("taskType") Integer taskType,@Param("taskTime") Integer taskTime,@Param("userID") Integer userID,@Param("taskLive") Integer taskLive);

    //（后台）2.2任务编辑 更新
    @Update("update task set taskName=#{taskName},taskType=#{taskType},taskTime=#{taskTime},taskDate=#{taskDate},userID=#{userID},taskLive=#{taskLive},taskClass=#{taskClass} where taskID=#{taskID}")
    Integer UpdateTheTask(Task task);

    //(日历所选日期的专注总时间)
    @Select("SELECT sum(taskTime) from task where DATE_FORMAT(taskDate,'%Y-%m-%d')='${date_time}' and userID=#{userID} and taskLive=true")
    Integer SelectSumTime(@Param("date_time")String date_time,@Param("userID")Integer userID);

    //在日历所选日期对应用户ID设置了多少个待办任务
    @Select("select count(*) as sumtodo from task where userID=#{userID} and DATE_FORMAT(taskDate,'%Y-%m-%d')='${date_time}' and taskType=false")
    Integer SelectSumTodo(@Param("date_time")String date_time,@Param("userID")Integer userID);

    //在日历所选日期对应用户ID设置了多少个专注任务
    @Select("select count(*) as sumclock from task where userID=#{userID} and DATE_FORMAT(taskDate,'%Y-%m-%d')='${date_time}' and taskType=true")
    Integer SelectSumClock(@Param("date_time")String date_time,@Param("userID")Integer userID);

    //在日历所选日期对应用户ID完成了多少个待办任务
    @Select("select count(*) as finishedtodo from task where userID=#{userID} and DATE_FORMAT(taskDate,'%Y-%m-%d')='${date_time}' and taskType=false and taskLive=true")
    Integer SelectFinishedTodo(@Param("date_time")String date_time,@Param("userID")Integer userID);

    //在日历所选日期对应用户ID完成了多少个专注任务
    @Select("select count(*) as finishedclock from task where userID=#{userID} and DATE_FORMAT(taskDate,'%Y-%m-%d')='${date_time}' and taskType=true and taskLive=true")
    Integer SelectFinishedClock(@Param("date_time")String date_time,@Param("userID")Integer userID);
    @Select("SELECT date_format(taskDate,'%y-%m-%d') toDoDate,count(DISTINCT userID) toDoUser FROM task where taskType=0 GROUP BY date_format(taskDate,'%y-%m-%d') ORDER BY date_format(taskDate,'%y-%m-%d')")
    List<Task> countAlltoDoUser();

    @Select("select taskTime,taskName FROM task where userID=22 and taskTime is not null;")
    List<Task> countOneUser();

    //echarts
    @Select("SELECT date_format(taskDate,'%Y-%m-%d') as date_time,count(*) count FROM task GROUP BY date_format(taskDate,'%Y-%m-%d') order BY date_time")
    List<Task> findAlltask();

    @Select("SELECT date_format(taskDate,'%y-%m-%d') taskDateToDo,count(*) countToDo FROM task where taskType=0 GROUP BY date_format(taskDate,'%y-%m-%d') order BY date_format(taskDate,'%y-%m-%d')")
    List<Task> findAlltaskToDo();

    @Select("SELECT date_format(taskDate,'%y-%m-%d') taskDateClock,count(*) countClock FROM task where taskType=1 GROUP BY date_format(taskDate,'%y-%m-%d') order BY date_format(taskDate,'%y-%m-%d')")
    List<Task> countAlltaskClock();

    @Select("SELECT date_format(taskDate,'%y-%m-%d') clockDate,count(DISTINCT userID) clockUser FROM task where taskType=1  GROUP BY date_format(taskDate,'%y-%m-%d') order BY date_format(taskDate,'%y-%m-%d')")
    List<Task>countAllclockUser();

}
